package database;

public class DatabaseQueries {
    public static String QUERY_SELECT_USER_ROLE_OF_USERNAME_AND_PASSWORD = "SELECT account_type FROM accounts WHERE username = ? AND password_hash = ?";
    
    public static String QUERY_INSERT_TO_ACCESS_LOG = "INSERT INTO access_log (ip_address, used_username, used_password_hash, access_date, successful) VALUES (?, ?, ?, ?, ?)";
    
    public static String QUERY_SELECT_STUDENT_ID = "SELECT student_id FROM students WHERE username = ?";
    
    public static String QUERY_SELECT_PARENT_ID = "SELECT parent_id FROM parents WHERE username = ?";
    
    public static String QUERY_SELECT_TEACHER_ID = "SELECT teacher_id FROM teachers WHERE username = ?";
    
    public static String QUERY_SELECT_STUDENT_NAME_CLASS_ID_AND_CLASS_NAME = 
            "SELECT first_name, last_name, name, class_id FROM students JOIN classes USING (class_id) WHERE student_id = ?";
    
    public static String QUERY_SELECT_STUDENT_NAME_PARENT_NAME_CLASS_ID_AND_CLASS_NAME = 
            "SELECT s.first_name, s.last_name, c.name, p.first_name, p.last_name, s.class_id FROM students s, parents p, classes c" +
            " WHERE s.parent_id = p.parent_id AND c.class_id = s.class_id AND p.parent_id = ?" ;
    
    public static String QUERY_SELECT_TEACHER_NAME_AND_USERNAME = "SELECT first_name, last_name, username FROM teachers WHERE teacher_id = ?";
    
    public static String QUERY_SELECT_PARENT_NAME_AND_USERNAME = "SELECT first_name, last_name, username FROM parents WHERE parent_id = ?";

    public static String QUERY_SELECT_USERNAME_OF_STUDENT = "SELECT username FROM students WHERE student_id = ?";
    
    public static String QUERY_SELECT_USERNAME_OF_PARENT = "SELECT username FROM parents WHERE parent_id = ?";
    
    public static String QUERY_SELECT_USERNAME_OF_TEACHER = "SELECT username FROM teachers WHERE teacher_id = ?";
    
    public static String QUERY_UPDATE_PASSWORD_OF_USERNAME_WITH_PASSWORD = "UPDATE accounts SET password_hash = ? WHERE username = ? AND password_hash = ?";
    
    public static String QUERY_UPDATE_MESSAGES_OF_USERNAME_TO_SEEN = "UPDATE messages SET seen = TRUE WHERE to_username = ?";
    
    public static String QUERY_SELECT_MESSAGES_OF_USERNAME = "SELECT message_id, from_username, to_username, timestamp, text, from_deleted, to_deleted FROM messages WHERE from_username = ? OR to_username = ?";
    
    public static String QUERY_SELECT_USER_ROLE_OF_USERNAME = "SELECT account_type FROM accounts WHERE username = ?";
    
    public static String QUERY_SELECT_STUDENT_FIRST_NAME = "SELECT first_name FROM students WHERE student_id = ?";
    
    public static String QUERY_SELECT_PARENT_FIRST_NAME = "SELECT first_name FROM parents WHERE parent_id = ?";
    
    public static String QUERY_SELECT_TEACHER_FIRST_NAME = "SELECT first_name FROM teachers WHERE teacher_id = ?";
    
    public static String QUERY_SELECT_STUDENT_LAST_NAME = "SELECT last_name FROM students WHERE student_id = ?";
    
    public static String QUERY_SELECT_PARENT_LAST_NAME = "SELECT last_name FROM parents WHERE parent_id = ?";
    
    public static String QUERY_SELECT_TEACHER_LAST_NAME = "SELECT last_name FROM teachers WHERE teacher_id = ?";
    
    public static String QUERY_SELECT_NEW_MESSAGES_OF_USERNAME = "SELECT message_id FROM messages WHERE to_username = ? AND seen = FALSE";
    
    public static String QUERY_SELECT_CLASSES = "SELECT class_id, name FROM classes ORDER BY name ASC";
    
    public static String QUERY_SELECT_SUBJECTS = "SELECT subject_id, name, yearclass FROM subjects ORDER BY name, yearclass ASC";
    
    public static String QUERY_SELECT_STUDENTS_OF_CLASS = "SELECT student_id, first_name, last_name, username, parent_id, class_id FROM students WHERE class_id = ? ORDER BY last_name, first_name ASC";
   
    public static String QUERY_SELECT_CLASS_NAME = "SELECT name FROM classes WHERE class_id = ?"; 
    
    public static String QUERY_SELECT_TEACHERS = "SELECT teacher_id, first_name, last_name, username FROM teachers ORDER BY last_name, first_name ASC";
    
    public static String QUERY_SELECT_SUBJECTS_OF_TEACHER = "SELECT s.subject_id, s.name, s.yearclass FROM subjects s, teachers_subjects ts"+
            " WHERE ts.teacher_id = ? AND ts.subject_id = s.subject_id ORDER BY s.name, s.yearclass";

    public static String QUERY_UPDATE_MESSAGE_OF_USERNAME_TO_FROM_DELETED = "UPDATE messages SET from_deleted = TRUE WHERE message_id = ? AND from_username = ?";
    
    public static String QUERY_UPDATE_MESSAGE_OF_USERNAME_TO_TO_DELETED = "UPDATE messages SET to_deleted = TRUE WHERE message_id = ? AND to_username = ?";
    
    public static String QUERY_REMOVE_MESSAGE_COMPLETELY_IF_DELETED_BY_BOTH = "DELETE FROM messages WHERE message_id = ? AND from_deleted = TRUE AND to_deleted = TRUE";
    
    public static String QUERY_SELECT_NOTICES_FOR_STUDENT = "SELECT notice_id, teacher_id, message, type, timestamp FROM notices" +
            " WHERE class_id = ? AND type <> 'p' ORDER BY timestamp DESC";
    
    public static String QUERY_SELECT_CLASS_FOR_STUDENT = "SELECT c.class_id FROM classes c,students s WHERE s.student_id = ? AND s.class_id = c.class_id";
    
    public static String QUERY_SELECT_STUDENT_FOR_PARENT = "SELECT student_id FROM students WHERE parent_id = ?";
    
    public static String QUERY_SELECT_NOTICES_FOR_PARENT = "SELECT notice_id, teacher_id, message, type, timestamp FROM notices" +
            " WHERE class_id = ? ORDER BY timestamp DESC";
    
    public static String QUERY_SELECT_GRADES_FOR_STUDENT = "SELECT g.gradebook_id, g.subject_id, g.grades FROM gradebook g, subjects s WHERE g.subject_id = s.subject_id AND g.student_id = ?";
    
    public static String QUERY_INSERT_MESSAGE = "INSERT INTO messages (from_username, to_username, timestamp, text) VALUES (?, ?, ?, ?)";
    
    public static String QUERY_SELECT_SUBSTITUTIONS_OF_DATE = "SELECT s.substitutor, t.teacher_id, t.subject_id, t.class_id, t.time FROM substitution s, absences a, timetables t" +
            " WHERE s.absence_id = a.absence_id AND s.timetable_id = t.timetable_id AND a.date = ?";
    
    public static String QUERY_SELECT_SUBSTITUTIONS_OF_DATE_AND_OF_CLASS = "SELECT s.substitutor, t.teacher_id, t.subject_id, t.time FROM substitution s, absences a, timetables t" +
            " WHERE s.absence_id = a.absence_id AND s.timetable_id = t.timetable_id AND a.date = ? AND t.class_id = ?";
    
    public static String QUERY_SELECT_SUBSTITUTIONS_OF_DATE_AND_OF_TEACHER = "SELECT s.substitutor, t.teacher_id, t.subject_id, t.class_id, t.time FROM substitution s, absences a, timetables t" +
            " WHERE s.absence_id = a.absence_id AND s.timetable_id = t.timetable_id AND a.date = ? AND (t.teacher_id = ? OR s.substitutor = ?)";
    
    public static String QUERY_SELECT_SUBJECT_NAME_AND_YEARCLASS = "SELECT name, yearclass FROM subjects WHERE subject_id = ?";
    
    public static String QUERY_REMOVE_NOTICE = "DELETE FROM notices WHERE notice_id = ? AND teacher_id = ?";
    
    public static String QUERY_INSERT_NOTICE = "INSERT INTO notices (teacher_id, class_id, message, type, timestamp) VALUES (?, ?, ?, ?, ?)";
    
    public static String QUERY_SELECT_ALL_TUTORAGES = "SELECT t.tutorage_id, t.subject_id, t.student_id, t.type, t.timestamp, c.name FROM tutorages t, students s, classes c" + 
            " WHERE t.student_id = s.student_id AND s.class_id = c.class_id";
    
    public static String QUERY_SELECT_TUTORAGES_OF_STUDENT = "SELECT t.tutorage_id, t.subject_id, t.student_id, t.type, t.timestamp, c.name FROM tutorages t, students s, classes c" + 
            " WHERE t.student_id = s.student_id AND s.class_id = c.class_id AND t.student_id = ?";
    
    public static String QUERY_SELECT_REMOTE_TUTORAGES_FOR_SUBJECT_WITHOUT_THOSE_OF_STUDENT = "SELECT t.tutorage_id, t.subject_id, t.student_id, t.type, t.timestamp, c.name FROM tutorages t, students s, classes c" + 
            " WHERE t.student_id = s.student_id AND s.class_id = c.class_id AND t.subject_id = ? AND t.student_id <> ? AND t.type <> ?";
    
    public static String QUERY_SELECT_STUDENT_NAME_PARENT_ID_AND_CLASS_ID = "SELECT first_name, last_name, username, parent_id, class_id FROM students WHERE student_id = ?";
    
    public static String QUERY_REMOVE_TUTORAGE = "DELETE FROM tutorages WHERE tutorage_id = ? AND student_id = ?";
    
    public static String QUERY_INSERT_TUTORAGE = "INSERT INTO tutorages (student_id, subject_id, type, timestamp) VALUES (?, ?, ?, ?)";
    
    public static String QUERY_SELECT_CLASSES_AND_SUBJECTS_OF_TEACHER = "SELECT DISTINCT class_id, subject_id FROM timetables WHERE teacher_id = ?";
    
    public static String QUERY_SELECT_TIMETABLE_OF_TEACHER_CLASS_AND_SUBJECT = "SELECT timetable_id FROM timetables WHERE teacher_id = ? AND class_id = ? AND subject_id = ?";
    
    public static String QUERY_SELECT_GRADES_OF_CLASS_AND_SUBJECT = "SELECT g.gradebook_id, g.student_id, g.grades FROM gradebook g, students s WHERE g.student_id = s.student_id AND g.subject_id = ? AND s.class_id = ?";
    
    public static String QUERY_SELECT_GRADES_OF_CLASS_AND_SUBJECT_WITH_ID = "SELECT g.gradebook_id, g.student_id, g.grades FROM gradebook g, students s WHERE g.student_id = s.student_id AND g.subject_id = ?" +
            " AND s.class_id = ? AND g.gradebook_id = ?";
    
    public static String QUERY_UPDATE_GRADES_OF_GRADEBOOK = "UPDATE gradebook SET grades = ? WHERE gradebook_id = ?";
            
    public static String QUERY_SELECT_ABSENCES_FOR_TEACHER_SINCE_DATE = "SELECT absence_id, date, from_timetable_unit, to_timetable_unit FROM absences WHERE teacher_id = ? AND date >= ? ORDER BY date DESC";
    
    public static String QUERY_SELECT_ALL_ABSENCES_SINCE_DATE = "SELECT absence_id, teacher_id, date, from_timetable_unit, to_timetable_unit FROM absences WHERE date >= ? ORDER BY date DESC";
    
    public static String QUERY_INSERT_ABSENCE = "INSERT INTO absences (teacher_id, date, from_timetable_unit, to_timetable_unit) VALUES (?, ?, ?, ?)";
    
    public static String QUERY_REMOVE_ABSENCE_OF_TEACHER = "DELETE FROM absences WHERE absence_id = ? AND teacher_id = ?";
    
    public static String QUERY_REMOVE_ABSENCE = "DELETE FROM absences WHERE absence_id = ?";
    
    public static String QUERY_SELECT_SUBJECT_ID = "SELECT subject_id FROM subjects WHERE name = ? AND yearclass = ?";
    
    public static String QUERY_INSERT_SUBJECT = "INSERT INTO subjects (name, yearclass) VALUES (?, ?)";
    
    public static String QUERY_UPDATE_SUBJECT = "UPDATE subjects SET name = ?, yearclass = ? WHERE subject_id = ?";
    
    public static String QUERY_REMOVE_SUBJECT = "DELETE FROM subjects WHERE subject_id = ?";
    
    public static String QUERY_SELECT_CLASS_ID = "SELECT class_id FROM classes WHERE name = ?";
    
    public static String QUERY_INSERT_CLASS = "INSERT INTO classes (name) VALUES (?)";
    
    public static String QUERY_REMOVE_CLASS = "DELETE FROM classes WHERE class_id = ?";
    
    public static String QUERY_SELECT_SUBJECT_IDS_OF_CLASS = "SELECT subject_id FROM classes_subjects WHERE class_id = ?";
    
    public static String QUERY_UPDATE_CLASS_NAME = "UPDATE classes SET name = ? WHERE class_id = ?";
    
    public static String QUERY_INSERT_CLASSES_SUBJECTS = "INSERT INTO classes_subjects (class_id, subject_id) VALUES (?, ?)";
    
    public static String QUERY_INSERT_GRADEBOOK = "INSERT INTO gradebook (subject_id, student_id, grades) VALUES (?, ?, ?)";
    
    public static String QUERY_REMOVE_CLASSES_SUBJECTS = "DELETE FROM classes_subjects WHERE class_id = ? AND subject_id = ?";
    
    public static String QUERY_REMOVE_TIMETABLES_OF_CLASS = "DELETE FROM timetables WHERE class_id = ? AND subject_id = ?";
    
    public static String QUERY_REMOVE_GRADEBOOK_OF_STUDENT_FOR_SUBJECT = "DELETE FROM gradebook WHERE student_id = ? AND subject_id = ?";
    
    public static String QUERY_REMOVE_USER = "DELETE FROM accounts WHERE username = ?";
    
    public static String QUERY_INSERT_ACCOUNT = "INSERT INTO accounts (username, password_hash, account_type) VALUES (?, ?, ?)";
    
    public static String QUERY_INSERT_TEACHER = "INSERT INTO teachers (username, first_name, last_name) VALUES (?, ?, ?)";
    
    public static String QUERY_INSERT_TEACHERS_SUBJECTS = "INSERT INTO teachers_subjects (teacher_id, subject_id) VALUES (?, ?)";
    
    public static String QUERY_SELECT_USERNAME = "SELECT username FROM accounts WHERE username = ?";
    
    public static String QUERY_SELECT_SUBJECT_IDS_OF_TEACHER = "SELECT subject_id FROM teachers_subjects WHERE teacher_id = ?";
    
    public static String QUERY_UPDATE_TEACHER_NAME = "UPDATE teachers SET first_name = ?, last_name = ? WHERE teacher_id = ?";
    
    public static String QUERY_REMOVE_TEACHERS_SUBJECTS = "DELETE FROM teachers_subjects WHERE teacher_id = ? AND subject_id = ?";
    
    public static String QUERY_REMOVE_TIMETABLES_OF_TEACHER = "DELETE FROM timetables WHERE teacher_id = ? AND subject_id = ?";
    
    public static String QUERY_UPDATE_PASSWORD_OF_USERNAME = "UPDATE accounts SET password_hash = ? WHERE username = ?";
    
    public static String QUERY_INSERT_PARENT = "INSERT INTO parents (username, first_name, last_name) VALUES (?, ?, ?)";
    
    public static String QUERY_INSERT_STUDENT = "INSERT INTO students (username, first_name, last_name, parent_id, class_id) VALUES (?, ?, ?, ?, ?)";
    
    public static String QUERY_UPDATE_PARENT_NAME = "UPDATE parents SET first_name = ?, last_name = ? WHERE parent_id = ?";
    
    public static String QUERY_UPDATE_STUDENT_NAME_AND_CLASS_ID = "UPDATE students SET first_name = ?, last_name = ?, class_id = ? WHERE student_id = ?";
    
    public static String QUERY_REMOVE_ALL_TIMETABLES_OF_CLASS = "DELETE FROM timetables WHERE class_id = ?";
    
    public static String QUERY_SELECT_EXISTENCE_OF_TEACHERS_SUBJECTS_WITH_TEACHER_ID_AND_SUBJECT_ID = "SELECT teacher_id FROM teachers_subjects WHERE teacher_id = ? AND subject_id = ?";
    
    public static String QUERY_SELECT_EXISTENCE_OF_CLASSES_SUBJECTS_WITH_CLASS_ID_AND_SUBJECT_ID = "SELECT class_id FROM classes_subjects WHERE class_id = ? AND subject_id = ?";
    
    public static String QUERY_SELECT_TIMETABLE_ID_WHERE_TEACHER_DAY_AND_UNIT = "SELECT timetable_id FROM timetables WHERE teacher_id = ? AND day = ? AND time = ?";
    
    public static String QUERY_INSERT_TIMETABLE = "INSERT INTO timetables (class_id, subject_id, teacher_id, day, time) VALUES (?, ?, ?, ?, ?)";
    
    public static String QUERY_SELECT_TIMETABLES_OF_CLASS = "SELECT timetable_id, subject_id, teacher_id, day, time FROM timetables WHERE class_id = ? ORDER BY day, time ASC";
    
    public static String QUERY_SELECT_TIMETABLES_OF_TEACHER = "SELECT timetable_id, subject_id, class_id, day, time FROM timetables WHERE teacher_id = ? ORDER BY day, time ASC";
    
    public static String QUERY_SELECT_SUBSTITUTION_IDS_WHERE_TEACHER_IS_SUBSTITUTOR = "SELECT s.substitution_id FROM substitution s, timetables t, absences a" +
            " WHERE s.absence_id = a.absence_id AND s.timetable_id = t.timetable_id" +
            " AND t.day = ? AND t.time BETWEEN ? AND ? AND a.date = ? AND s.substitutor = ?";
    
    public static String QUERY_REMOVE_SUBSTITUTION = "DELETE FROM substitution WHERE substitution_id = ?";
    
    public static String QUERY_SELECT_SUBSTITUTIONS_SINCE_DATE = "SELECT s.substitutor, t.teacher_id, t.subject_id, t.class_id, t.time, a.date, s.substitution_id FROM substitution s, absences a, timetables t" +
            " WHERE s.absence_id = a.absence_id AND s.timetable_id = t.timetable_id AND a.date >= ?";
    
    public static String QUERY_SELECT_TIMETABLE = "SELECT subject_id, teacher_id, class_id, day, time FROM timetables WHERE timetable_id = ?";
    
    public static String QUERY_SELECT_ABSENCE = "SELECT teacher_id, date, from_timetable_unit, to_timetable_unit FROM absences WHERE absence_id = ?";
    
    public static String QUERY_SELECT_TEACHING_TEACHERS = "SELECT DISTINCT teacher_id FROM timetables WHERE day = ? AND time = ?";
    
    public static String QUERY_SELECT_MISSING_TEACHERS = "SELECT DISTINCT teacher_id FROM absences WHERE date = ? AND from_timetable_unit <= ? AND to_timetable_unit >= ?";
    
    public static String QUERY_SELECT_SUBSTITUTORS_IN_DATE_AND_TIME = "SELECT s.substitutor FROM substitution s, absences a, timetables t" +
            " WHERE s.absence_id = a.absence_id AND s.timetable_id = t.timetable_id AND a.date = ? AND t.time = ?";
    
    public static String QUERY_INSERT_SUBSTITUTION = "INSERT INTO substitution (absence_id, timetable_id, substitutor) VALUES (?, ?, ?)";
    
    public static String QUERY_SELECT_TIMETABLE_ID_AND_ABSENCE_ID_OF_SUBSTITUTION = "SELECT timetable_id, absence_id FROM substitution WHERE substitution_id = ?";
    
    public static String QUERY_SELECT_TIMETABLE_ID_WHERE_TEACHER_DAY_AND_UNIT_BETWEEN = "SELECT timetable_id FROM timetables WHERE teacher_id = ? AND day = ? AND time BETWEEN ? AND ?";
    
    private DatabaseQueries() {
    }
}
